package com.hrd.common.util.file;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.List;
import java.util.Map;

/***
 * 适用于大数据量导出
 * @author langx
 *
 */
public class SXSSFExportExcel {

	  /**
	   * 创建导出标题
	   *
	   * @param titles
	   * @param sheetName
	   * @return
	   */
	  public static Sheet createHSSFWorkbookTitle(SXSSFWorkbook workbook, String[] titles, String sheetName,Map<String, Integer> map) {
		  
	        Sheet sheet = workbook.createSheet(sheetName);
	
	        CellStyle style = workbook.createCellStyle();
	        DataFormat format = workbook.createDataFormat();
	        style.setDataFormat(format.getFormat("@"));
	
	        Row row = sheet.createRow(0);
	        CellStyle cellStyleTitle= workbook.createCellStyle();  
	        Font font = workbook.createFont();
	        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
	        cellStyleTitle.setFont(font);//选择需要用到的字体格式
	        cellStyleTitle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
	        cellStyleTitle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
	        for (int celLength = 0; celLength < titles.length; celLength++) {
	            // 创建相应的单元格
	            Cell cell = row.createCell(celLength);
	            cell.setCellValue(titles[celLength]);
	            sheet.setDefaultColumnStyle(celLength, style);
	            //自适应宽度
	            int width = map.get(celLength+"")==null ? 0 : map.get(celLength+"");
	            int widthCell = (titles[celLength].toString().getBytes().length+1)*256 > 255*256 ? 255*256 : (titles[celLength].toString().getBytes().length+1)*256;
	            //列宽基数：0.72
	            if (width<widthCell) {
	            	sheet.setColumnWidth(celLength, widthCell);
	            	map.put(celLength+"", new Integer(widthCell));
				}else{
					sheet.setColumnWidth(celLength, width);
				}
	            
	            cell.setCellStyle(cellStyleTitle);
	        }
		  
	
	    return sheet;
	  }
	
	/**
     * 创建导出数据 2007
     *
     * @param titles
     * @param sheetName
     * @return
     */
    public static SXSSFWorkbook createSXSSFWorkbookInfo(String[] titles, List<Map<String, String>> infoList, 
    		String sheetName,Sheet sheet,SXSSFWorkbook xBook,Map<String, Integer> map,int startRow) {
        // 生成一个表格
    	CellStyle style = xBook.createCellStyle();
        DataFormat format = xBook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));
        
    	int rowNumber = 0;
        
        if (infoList != null && infoList.size() > 0) {
        	CellStyle cellStyle= xBook.createCellStyle();  
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框    
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框    
            for (Map<String, String> info : infoList) {
                rowNumber++;
                Row row = sheet.createRow(startRow + rowNumber );
                for (int celLength = 0; celLength < titles.length; celLength++) {
                    // 创建相应的单元格
                    Cell cell = row.createCell(celLength);
                    cell.setCellValue(info.get(titles[celLength]));
                    sheet.setDefaultColumnStyle(celLength, style);
                    //自适应宽度
                    int width = map.get(celLength+"")==null ? 0 : map.get(celLength+"");
                    int widthCell = (info.get(titles[celLength]).toString().getBytes().length+1) * 256 > 255*256 ? 255*256:(info.get(titles[celLength]).toString().getBytes().length+1)*256;
                    if (width<widthCell) {
                    	sheet.setColumnWidth(celLength,widthCell);
                    	map.put(celLength+"", new Integer(widthCell));
                    	
        			}else{
        				sheet.setColumnWidth(celLength, width);
        			}
                    
                    cell.setCellStyle(cellStyle);
                }
            }
        }
        
        return xBook;
    }
    /**
     * 输出附件
     *
     * @param titles
     * @return
     */
    public static void writeExcelFileForSXSSFWorkbook(HttpServletRequest request, HttpServletResponse response, SXSSFWorkbook workbook, String[] titles, String fileName) {
      // 根据列名填充相应的数据
      ServletOutputStream out = null;
      try {
        out = response.getOutputStream();
        response.reset();
        response.setContentType("application/msexcel;charset=utf-8");
      //response.setHeader("content-disposition", "attachment;filename=" + new String((fileName).getBytes("UTF-8"), "ISO8859-1"));
        String userAgent = request.getHeader("user-agent");
        if (userAgent != null && userAgent.indexOf("Edge") >= 0 ) {
        	fileName = URLEncoder.encode(fileName, "UTF8"); 
        }else if (userAgent.indexOf("Firefox") >= 0 || userAgent.indexOf("Chrome") >= 0
                || userAgent.indexOf("Safari") >= 0) {
            fileName = new String((fileName).getBytes("UTF-8"), "ISO8859-1");
        } else {
            fileName = URLEncoder.encode(fileName, "UTF8"); //其他浏览器
        }
        response.setHeader("content-disposition", "attachment;filename=" + fileName);
        
        workbook.write(out);
        out.flush();
        out.close();
      } catch (IOException e) {
        e.printStackTrace();
      } finally {
        if (out != null) {
          try {
            out.flush();
            out.close();
          } catch (IOException e) {
            e.printStackTrace();
          }
        }
      }
    }

    /**
     * 创建导出数据 2007
     *
     * @param titles
     * @param sheetName
     * @return
     */
    public static SXSSFWorkbook createSXSSFWorkbook(String[] titles, List<Map<String, String>> infoList,
                                                        String sheetName,Sheet sheet,SXSSFWorkbook xBook,Map<String, Integer> map) {
        // 生成一个表格
        CellStyle style = xBook.createCellStyle();
        DataFormat format = xBook.createDataFormat();
        style.setDataFormat(format.getFormat("@"));

        int rowNumber = 0;

        if (infoList != null && infoList.size() > 0) {
            CellStyle cellStyle= xBook.createCellStyle();
            cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            for (Map<String, String> info : infoList) {
                rowNumber++;
                Row row = sheet.createRow(rowNumber );
                for (int celLength = 0; celLength < titles.length; celLength++) {
                    // 创建相应的单元格
                    Cell cell = row.createCell(celLength);
                    cell.setCellValue(info.get(titles[celLength]));
                    sheet.setDefaultColumnStyle(celLength, style);
                    //自适应宽度
                    int width = map.get(celLength+"")==null ? 0 : map.get(celLength+"");
                    int widthCell = (info.get(titles[celLength]).toString().getBytes().length+1) * 256 > 255*256 ? 255*256:(info.get(titles[celLength]).toString().getBytes().length+1)*256;
                    if (width<widthCell) {
                        sheet.setColumnWidth(celLength,widthCell);
                        map.put(celLength+"", new Integer(widthCell));

                    }else{
                        sheet.setColumnWidth(celLength, width);
                    }

                    cell.setCellStyle(cellStyle);
                }
            }
        }

        return xBook;
    }



}
